import xlrd
import xlwt
import sys
MAX_INT = sys.maxsize

class ReadExcel (object):
    def __init__(self, filename, sheetindex):
        self.wb = xlrd.open_workbook(filename)
        self.sh = self.wb.sheet_by_index(sheetindex)
        self.title = self.sh.row(0)
    # 读入excel中的人体数据
    def read_data_line(self):
        n_rows = self.sh.nrows
        rows_data = []
        for i in range(1, n_rows):
            rows_data.append(self.sh.row(i))
        return rows_data;
    # 根据身高筛选邻近的数据，单位为mm，这里可以采用二分法查找提高效率
    def get_near_data(self, height, height_index, filter_data):
        up_limit = height + 5
        down_limit = height - 5
        after_filter_data = []
        for i in range(0, len(filter_data)):
            if int(filter_data[i][height_index].value) >= up_limit:
                break
            if int(filter_data[i][height_index].value) >= down_limit:
                after_filter_data.append(filter_data[i])
        return after_filter_data
    # 计算数据列表中的元素与目标数据元素的差值
    def get_data_lost(self, data, target, prop_index, prop_weight):
        lost = 0
        for i in range(len(target)):
            lost += abs(int(data[prop_index[i]].value) - target[i]) * prop_weight[i]
        # print(lost)
        return lost
    # 获取最接近的id
    def get_nearest_id(self, filter_data, target_data, target_prop_index, target_prop_weight):
        res = [MAX_INT, 0]
        for i in range(len(filter_data)):
            local_value = self.get_data_lost(filter_data[i], target_data, target_prop_index, target_prop_weight)
            if local_value < res[0]:
                res[0] = local_value
                res[1] = i
        # print(res[1])
        return filter_data[res[1]][0].value[:4]


def get_nearest_model_filename(filename, sheet_index, target_data, target_prop_index, target_prop_weight):
    r = ReadExcel(filename, sheet_index)
    data = r.read_data_line()
    filter_data = r.get_near_data(1650, 1, data)
    res_id = r.get_nearest_id(filter_data, target_data, target_prop_index, target_prop_weight)
    return res_id

if __name__ == '__main__':
    # 目标数据对应的尺寸数据分别为 身高、肩宽、手臂长、胸围、腰围、臀围、小腿长
    target_data = [1650, 440, 490, 890, 760, 910, 460]
    # 目标数据各尺寸数据对应的data数据的索引，注意0索引是id编号
    target_prop_index = [1, 2, 4, 7, 10, 13, 14]
    # 目标数据各尺寸数据对应的权重
    target_prop_weight = [1, 1, 1, 2, 2, 2, 1]

    filename = get_nearest_model_filename('caesar_male_data.xlsx', 0, target_data, target_prop_index, target_prop_weight)
    print(filename)
    

    